<?php
class mdl_formulir_report extends CI_Model{ 
	
	var $tabel = 'tm_formulir';
	
	function __construct(){
		parent::__construct();
	} 

	function data($p=array()){
	
		$where = '';
		
		if( isset($p['column']) && isset($p['keyword']) ){
			if($p['column'] != '' || $p['keyword'] != ''){
				if($p['column'] == 'all'){
					$where = " AND ( form_principal_name like '%".pTxt($p['keyword'])."%' 
									 OR form_principal_address like '%".pTxt($p['keyword'])."%' 
									 OR form_obligee_name like '%".pTxt($p['keyword'])."%' 
									 OR form_obligee_address like '%".pTxt($p['keyword'])."%' 
									 OR form_bank_garansi_type like '%".pTxt($p['keyword'])."%' 
									 OR form_contract_value like '%".pTxt($p['keyword'])."%' 
									 OR form_guarantee_value like '%".pTxt($p['keyword'])."%' 
									 OR form_document_number like '%".pTxt($p['keyword'])."%' 
									 OR form_document_desc like '%".pTxt($p['keyword'])."%' 
									 OR form_job like '%".pTxt($p['keyword'])."%' 
									 OR form_as_long_day like '%".pTxt($p['keyword'])."%' 
									 OR marketing_name like '%".pTxt($p['keyword'])."%' 
									 OR cur_name like '%".pTxt($p['keyword'])."%' 
									 OR cur_code like '%".pTxt($p['keyword'])."%')";
				}else{				
					$where = " AND ".$p['column']." like '%".pTxt($p['keyword'])."%' ";
				}
			}
		}
				
		if(isset($p['status']) && trim($p['status']) !=""){
			$where .= " AND a.form_status IN (".dbClean($p['status']).")";
		}
		
		if( isset($p['start_date']) && trim($p['start_date']) !="" ){
			$where .=" AND date_format(a.form_tgl_terbit_bank_garansi,'%Y-%m-%d') >= '".dbClean($p['start_date'])."'";
		}
		
		if( isset($p['end_date']) && trim($p['end_date']) !="" ){
			$where .=" AND date_format(a.form_tgl_terbit_bank_garansi,'%Y-%m-%d') <= '".dbClean($p['end_date'])."'";
		}
				
		if( isset($p['asuransi']) && trim($p['asuransi']) !="" ){
			$where .=" AND d.asuransi_id = '".dbClean($p['asuransi'])."' ";
		}
				
		if( isset($p['form_id']) && trim($p['form_id']) !="" ){
			$where .=" AND a.form_id = '".dbClean($p['form_id'])."' ";
		}
		
		$sql = "
		 	select 
				SQL_CALC_FOUND_ROWS *,a.time_add as tgl_entry
			FROM ".$this->tabel." a, tm_pialang_marketing b, tm_currency c,tm_pialang_asuransi d
			WHERE a.form_marketing = b.marketing_id
				AND a.form_mata_uang = c.cur_id
				AND a.form_asuransi = d.asuransi_id
				".$where."
			ORDER BY a.form_id DESC 
		";	 
		
		if( isset($p['limit']) && isset($p['offset']) ){
			$offset = empty($p['offset'])?0:$p['offset'];				
			$sql .= " LIMIT ".$offset.",".$p['limit']." ";
		}
		$query = $this->db->query($sql);
		
		$found_rows = $this->db->query('SELECT FOUND_ROWS() as found_rows');

		return array(
			"data" 	=> $query->result(),
			"count"	=> $found_rows->row()
		);
	}	
}